# Maestro is a music database search utility
# Copyright (C) 2010 Robert Mens
#
# This program is free software; you can redistribute it and/or
# modify it under the terms of the GNU General Public License
# as published by the Free Software Foundation; either version 2
# of the License, or (at your option) any later version.
#
# This program is distributed in the hope that it will be useful,
# but WITHOUT ANY WARRANTY; without even the implied warranty of
# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
# GNU General Public License for more details.
#
# You should have received a copy of the GNU General Public License
# along with this program; if not, write to the Free Software
# Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA  02110-1301, USA.

from __future__ import print_function

import apsw, os, settings

class MusicDB:
    def __init__(self, filename):        
        self.con = apsw.Connection(filename)

    def close(self):
        self.con.close()

    def create_new_db(self):
        self.con.cursor().execute("CREATE TABLE music(id integer primary key autoincrement, filename text unique not null, title text, artist text, album text, track text, year text, genre text)")        
        self.con.cursor().execute("CREATE INDEX musicidx on music (filename, title, artist, album, track, year, genre)")

    def add_song(self, filename="", title="", artist="", album="", track="", year="", genre=""):
        try:
            self.con.cursor().execute("INSERT INTO music('filename','title', 'artist', 'album', 'track', 'year', 'genre') VALUES (?,?,?,?,?,?,?)", (filename, title, artist, album, track, year, genre))
        except:
            return False
        
        return True

    def get_files(self):
        files = []
        rows = self.con.cursor().execute("SELECT filename FROM music")
        for row in rows:
            files.append(row[0])
            
        return files

    def remove_song(self, filename):
        self.con.cursor().execute("DELETE FROM music WHERE filename=?", [filename])
    
    def file_exists_in_database(self, filename):
        cursor = self.con.cursor()
        rows = list(cursor.execute("SELECT * FROM music WHERE filename=?", [filename]))
        if len(rows) > 0: return True

        return False

    # Traag!
    def get_random_songs(self, amount=10):
        results = []

        for x in range(amount):
            row = self.con.cursor().execute("SELECT * FROM music ORDER BY RANDOM() LIMIT 1; ").next()
            results.append(row)

        return results

    def merge_query(self, arguments):
        result = ""
        for arg in arguments:
            if len(arg.split()) > 1:
                result = result + '"' + arg +'" '
            else:
                result = result + arg + ' '
        return result

    def split_query(self, query):
        result = []
        merging = False
        merged = ""
        
        for item in query.split():
            if merging == True:
                
                if item[len(item)-1] == '"' or item[len(item)-1] == "'":
                    merging = False
                    merged = merged + item
                    result.append(merged[1:-1])
                else:
                    merged = merged + item + " "
            else:
                if item[0] == '"' or item[0] == "'":
                    merging = True
                    merged = item + " "
                else:
                    result.append(item)

        return result

    def get_songs(self, query):

        #TODO: sanitize query for sql injection as SQLite won't allow for parameterized queries with views!

        qbits = self.split_query(query.lower())
        results = []       
        view_prefix = "tmpview_"
        view_number = 0
        search_target = "music"
        
        # Handle special cases
        if qbits[0].lower() == ":random":
            num = 10
            if len(qbits) == 2:
                if qbits[1].isdigit():
                    num = int(qbits[1])

            return self.get_random_songs(num)

        # Grab relevant records from the database and put them in a temporary view, recursively.
        for qbit in qbits:
            view_name = view_prefix + str(view_number)

            if not qbit.find('artist:') == -1:
                self.con.cursor().execute("create temporary view "+view_name+" as select * from "+search_target+" where artist like '%"+qbit[len('artist:'):]+"%'")
            elif not qbit.find('artist=') == -1:
                self.con.cursor().execute("create temporary view "+view_name+" as select * from "+search_target+" where artist = '"+qbit[len('artist='):]+"' COLLATE NOCASE")
            elif not qbit.find('title:') == -1:
                self.con.cursor().execute("create temporary view "+view_name+" as select * from "+search_target+" where title like '%"+qbit[len('title:'):]+"%'")
            elif not qbit.find('title=') == -1:
                self.con.cursor().execute("create temporary view "+view_name+" as select * from "+search_target+" where title = '"+qbit[len('title='):]+"' COLLATE NOCASE")
            elif not qbit.find('album:') == -1:
                self.con.cursor().execute("create temporary view "+view_name+" as select * from "+search_target+" where album like '%"+qbit[len('album:'):]+"%'")
            elif not qbit.find('album=') == -1:
                self.con.cursor().execute("create temporary view "+view_name+" as select * from "+search_target+" where album = '"+qbit[len('album='):]+"' COLLATE NOCASE")        
            elif not qbit.find('filename:') == -1:
                self.con.cursor().execute("create temporary view "+view_name+" as select * from "+search_target+" where filename like '%"+qbit[len('filename:'):]+"%'")
            elif not qbit.find('filename=') == -1:
                self.con.cursor().execute("create temporary view "+view_name+" as select * from "+search_target+" where filename = '"+qbit[len('filename='):]+"' COLLATE NOCASE")    
            else:
                self.con.cursor().execute("create temporary view "+view_name+" as select * from "+search_target+" where title like '%"+qbit+"%' or artist like '%"+qbit+"%' or album like '%"+qbit+"%' or filename like '%"+qbit+"%'")

            search_target = view_name
            view_number += 1

        # Get the results from the final temporary view.
        for row in self.con.cursor().execute("select * from " + search_target):
            results.append(row) 

        # Drop all the used temporary views -- probably not neccesary since program execution halts shortly afterwards anyway.
        for i in range (0, view_number):
            view_name = view_prefix + str(i)
            self.con.cursor().execute("drop view if exists "+view_name)

        return results

    
if __name__ == "__main__":
    pass
    
    
    
